﻿using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.Threading.Tasks;
using System.Data;
using System.Data.SqlClient;

namespace DAL
{
    public class sqlHelper
    {
        static string strConn = System.Configuration.ConfigurationManager.ConnectionStrings["AiXiu"].ConnectionString;//"Data Source=.;Initial Catalog=AiXiu;Integrated Security=True";

        /// <summary>
        /// 查询数据集
        /// </summary>
        /// <param name="sql">T-sql语句</param>
        /// <param name="IsProc">是否存储过程</param>
        /// <param name="sp">参数化数组</param>
        /// <returns>满足条件的数据集</returns>
        public static DataTable Query(string sql, bool IsProc, params SqlParameter[] sp)
        {
            DataTable dt = new DataTable();
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn);
                if (IsProc)
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                }
                cmd.Parameters.AddRange(sp);
                SqlDataAdapter sda = new SqlDataAdapter(cmd);
                sda.Fill(dt);
                return dt;
            }
        }

        /// <summary>
        /// 增删改
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="IsProc"></param>
        /// <param name="sp"></param>
        /// <returns></returns>
        public static int ExecuteNonQuery(string sql, bool IsProc, params SqlParameter[] sp)
        {
            int res = -1;
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn);
                if (IsProc)
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                }
                cmd.Parameters.AddRange(sp);
                res = cmd.ExecuteNonQuery();
                return res;
            }
        }

        /// <summary>
        /// 查询数据 返回满足条件的首行首列
        /// </summary>
        /// <param name="sql"></param>
        /// <param name="IsProc"></param>
        /// <param name="sp"></param>
        /// <returns></returns>
        public static object ExecuteScalar(string sql, bool IsProc, params SqlParameter[] sp)
        {
            object obj = new object();
            using (SqlConnection conn = new SqlConnection(strConn))
            {
                conn.Open();
                SqlCommand cmd = new SqlCommand(sql, conn);
                if (IsProc)
                {
                    cmd.CommandType = CommandType.StoredProcedure;
                }
                cmd.Parameters.AddRange(sp);
                obj = cmd.ExecuteScalar();
                return obj;
            }
        }
    }

}
